package com.example.demo.xcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;

import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalTime;
import java.util.*;

/**
 * @author FENGYU
 * @description
 * @date 2023/8/31 15:15
 */
public class EasyEcellMain {
    public static Map<String, TUser0> map = new TreeMap<>();
    public static List<TUser0> users0 = new ArrayList<>();
    public static List<TUser0> save = new ArrayList<>();
    public static List<TUser> users = new ArrayList<>();
    public static List<TUser1> users1 = new ArrayList<>();

    public static Map<String, TUser0> users0Map = new HashMap<>();
    public static Map<String, TUser1> users1Map = new HashMap<>();
    public static List<TUser> users2 = new ArrayList<>();
    public static List<String> comList = new ArrayList<>();


    // 读入写入数据库
    public static void main(String[] args) {
        importFile1();
        importFile0();

        //importFile();


//        map.keySet().forEach(key -> {
//            TUser0 tUser0 = map.get(key);
//            if (negative(tUser0.getPaidAmount()) > 0) {
//                try {
////                    if (comList.contains(tUser0.getXprojectCode())) {
////                        if (perinumAble(tUser0.getPeriodNum()) && tUser0.getReleaseAmount() != null && negative(tUser0.getReleaseAmount()) <= 0) {
////                            save.add(tUser0);
////                        } else {
////                            users0.add(tUser0);
////                        }
////                    } else {
////                        users0.add(tUser0);
////                    }
//                    users0.add(tUser0);
//
//                } catch (Exception e) {
//                    throw new RuntimeException(e);
//                }
//            }
//        });


        System.out.println("去重负提成订单 users0Map 生成条数：" + users0Map.size());
        System.out.println("去重负提成订单 users1Map 生成条数：" + users1Map.size());

        String ddd = "( xp.xproject_code = '%s' AND contact.CODE = '%s' AND us.user_code = '%s' AND ur.PERIOD_NUM = '%s'" +
                "  AND ur.RELEASE_PERCENT = '%s' AND ur.PLUS_PERCENT = '%s' AND ur.RELEASE_AMOUNT!= '%s'  ) OR ";


        StringBuffer stringBuffer = new StringBuffer();

//        for (TUser0 tUser0 : users0) {
//            String bbb = String.format(ddd, tUser0.getXprojectCode(),
//                    tUser0.getCode(),
//                    tUser0.getUserCode(),
//                    tUser0.getPeriodNum()
//                    , tUser0.getReleasePercent() == null ? "" : tUser0.getReleasePercent().replace(",", "")
//                    , tUser0.getPlusPercent() == null ? "" : tUser0.getPlusPercent().replace(",", "")
//                    , tUser0.getReleaseAmount() == null ? "" : tUser0.getReleaseAmount().replace(",", "")
//            );
//            stringBuffer.append(bbb);
//
//        }
//        System.out.println(stringBuffer);

        //用excel的数据【提成基数/发放金额】，去更新数据库的提成明细 ，提成分配 的数据

        String pppp = "UPDATE `bosum_erp`.`user_release` " +
                "SET `RELEASE_AMOUNT` = '%s'," +
                "`STATUS` = '已发放'," +
                "`plus_value` = '%s'  " +
                "WHERE `USER_RELEASE_ID` = '%s';";

        users1Map.keySet().forEach(key -> {

            TUser0 tUser0 = users0Map.get(key);
            TUser1 tUser1 = users1Map.get(key);

            if (tUser0 != null && tUser1 != null
                    && tUser0.getReleaseAmount() != null
                    && tUser0.getPlusValue() != null
                    && tUser1.getUserReleaseId() != null) {
                String bbb = String.format(pppp,
                        tUser0.getReleaseAmount().replace(",", ""),
                        tUser0.getPlusValue().replace(",", ""),
                        tUser1.getUserReleaseId());
                System.out.println(bbb);
            }
        });


        // excelOutPut4();
        //  excelOutPut3();


    }

    public static boolean inDate(String sDate) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        Date date = sdf.parse(sDate);

        LocalDate begin = LocalDate.of(2023, 8, 31);     // 9.1 ~ 9.29
        LocalDate end = LocalDate.of(2023, 9, 29);

        Instant instant1 = Timestamp.valueOf(begin.atTime(LocalTime.MIDNIGHT)).toInstant();
        Instant instant2 = Timestamp.valueOf(end.atTime(LocalTime.MIDNIGHT)).toInstant();
        Date startTime = Date.from(instant1);
        Date endTime = Date.from(instant2);


        return date.after(startTime) && date.before(endTime);
    }


    public static void excelOutPut2() {

        // xls文件最大行数：65536
        // xlsx文件最大行数：1048576
        // 文件位置
        String path = "C:\\Users\\FENG\\Desktop\\ffff\\2023-11-01提成发放导出数据 (内勤20150101-20231101).xls";
        // 创建 easyexcel 的写出类构造器 参数 告诉构造器，我的excel将来要写到哪里，以及execl中数据是基于哪个java对象模板创建的
        ExcelWriter excelWriter = EasyExcel.write(path, TUser.class).build();

        // 创建 shee 构造器
        WriteSheet sheet = EasyExcel.writerSheet(0, "2023-11-01提成发放导出数据 (内勤20150101-20231101)").build();
        // 使用excel 对象数据写到创建sheet当中
        excelWriter.write(users0, sheet);

        excelWriter.finish();
        System.out.println("导出完毕");


    }

    // 往excel 中做个sheet中写入同类型数据
    public static void excelOutPut3() {

        // xls文件最大行数：65536
        // xlsx文件最大行数：1048576
        // 文件位置
        String path = "C:\\Users\\FENG\\Desktop\\ffff\\2024-01-05提成发放导出数据=退费&换项明细.xls";
        // 创建 easyexcel 的写出类构造器 参数 告诉构造器，我的excel将来要写到哪里，以及execl中数据是基于哪个java对象模板创建的
        ExcelWriter excelWriter = EasyExcel.write(path, TUser0.class).build();

        // 创建 shee 构造器
        WriteSheet sheet = EasyExcel.writerSheet(0, "2024-01-05提成发放导出数据=退费&换项明细").build();
        // 使用excel 对象数据写到创建sheet当中
        excelWriter.write(users0, sheet);

        excelWriter.finish();
        System.out.println("导出完毕");
    }

    public static void excelOutPut4() {

        // xls文件最大行数：65536
        // xlsx文件最大行数：1048576
        // 文件位置
        String path = "C:\\Users\\FENG\\Desktop\\ffff\\2023-11-02提成发放导出数据-锁定-不包含班主任-重复订单2.xls";
        // 创建 easyexcel 的写出类构造器 参数 告诉构造器，我的excel将来要写到哪里，以及execl中数据是基于哪个java对象模板创建的
        ExcelWriter excelWriter = EasyExcel.write(path, TUser0.class).build();

        // 创建 shee 构造器
        WriteSheet sheet = EasyExcel.writerSheet(0, "2023-11-02提成发放导出数据-锁定-不包含班主任-重复订单2").build();
        // 使用excel 对象数据写到创建sheet当中
        excelWriter.write(save, sheet);

        excelWriter.finish();
        System.out.println("导出完毕");
    }

    public static void importFile0() {
        // String path = "C:\\Users\\FENG\\Desktop\\ffff\\12月提成-合并最终版---1月8号_-1165293050.xlsx";
        // String path = "C:\\Users\\FENG\\Desktop\\ffff\\11月提成-1205_-25620802.xlsx";
        String path = "C:\\Users\\FENG\\Desktop\\ffff\\10月线下提成-提交流程_664245616.xlsx";
        // 读入构造器：文件位置，读取成哪个Java对象，读取监听器
        ExcelReader excelReader = EasyExcel.read(path, TUser0.class, new EasyExcelListener0()).build();
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        excelReader.read(readSheet);
        excelReader.finish();
    }

    public static void importFile1() {
        // String path = "C:\\Users\\FENG\\Desktop\\ffff\\12月提成-合并最终版---1月8号_-1165293050.xlsx";
        // String path = "C:\\Users\\FENG\\Desktop\\ffff\\11月提成-1205_-25620802.xls";
        String path = "C:\\Users\\FENG\\Desktop\\ffff\\10月线下提成-提交流程_664245616---【与数据库对比发放金额不一致的数据】-数据.xls";
        // 读入构造器：文件位置，读取成哪个Java对象，读取监听器
        ExcelReader excelReader = EasyExcel.read(path, TUser1.class, new EasyExcelListener1()).build();
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        excelReader.read(readSheet);
        excelReader.finish();
    }

    public static void importFile() {
        String path = "C:\\Users\\FENG\\Downloads\\2023年1-9月提成发放表(1).xlsx";
        // 读入构造器：文件位置，读取成哪个Java对象，读取监听器
        ExcelReader excelReader = EasyExcel.read(path, TUser.class, new EasyExcelListener()).build();
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        excelReader.read(readSheet);
        excelReader.finish();
    }

}
